/**
 * author:Administrator
 * date:2013-5-21
 */
package com.roc.web.action.profitShare;

import java.util.ArrayList;
import java.util.List;

import com.roc.page.BaseSearchCondition;
import com.roc.page.WhereHQLFieldBean;

/**
 * 
 * @author zx
 *
 */
public class ProfitShareQueryDetailSearchCondition extends BaseSearchCondition {
	

	private String orgCode;//机构号
	private String accountNo;//代理商账号
	private String minDate;//开始时间
	private String maxDate;//结束时间 

	/* (non-Javadoc)
	 * @see com.roc.page.BaseSearchCondition#getOrderPartOfHQL()
	 */
	@Override
	protected String getOrderPartOfHQL() {
		// TODO Auto-generated method stub
		
		return "   ";
	}

	/* (non-Javadoc)
	 * @see com.roc.page.BaseSearchCondition#getRowCountHQL()
	 */
	@Override
	protected String getRowCountHQL() {
		return " select count(*) from ("+getSql()+") o where 1=1  ";
	}

	/* (non-Javadoc)
	 * @see com.roc.page.BaseSearchCondition#getSearchHQL()
	 */
	@Override
	protected String getSearchHQL() {
		return getSql();
	}

	/* (non-Javadoc)
	 * @see com.roc.page.BaseSearchCondition#getWhereHQLFieldBeanList()
	 */
	@Override
	protected List<WhereHQLFieldBean> getWhereHQLFieldBeanList() {
		List<WhereHQLFieldBean> filedBeanList = new ArrayList<WhereHQLFieldBean>();
	
		return filedBeanList;
	}

	/**
	 * @return the accountNo
	 */
	public String getAccountNo() {
		return accountNo;
	}

	/**
	 * @param accountNo the accountNo to set
	 */
	public void setAccountNo(String accountNo) {
		this.accountNo = accountNo;
	}

	

	public String getOrgCode() {
		return orgCode;
	}

	public void setOrgCode(String orgCode) {
		this.orgCode = orgCode;
	}

	public String getMinDate() {
		return minDate;
	}

	public void setMinDate(String minDate) {
		this.minDate = minDate;
	}

	public String getMaxDate() {
		return maxDate;
	}

	public void setMaxDate(String maxDate) {
		this.maxDate = maxDate;
	}
	
	
	public String getSql(){
		
		StringBuffer stb = new StringBuffer("");
		stb.append("  SELECT X.*,CASE WHEN COMM_MODE = '3' THEN round(TO_CHAR((MERFEE- least(AMOUNT * PROFIT_FEE / 100,max_fee))*PROFIT_SETTLE_RATE/100),2)");
		stb.append(" ELSE round(TO_CHAR((TO_NUMBER(MERFEE) - AMOUNT * PROFIT_FEE / 100)*PROFIT_SETTLE_RATE/100),2)  END AS fenrun_AMT");
		stb.append(" FROM (SELECT V.*, TP.MAX_FEE, TP.PROFIT_FEE, TP.PROFIT_SETTLE_RATE,TP.MCC_TYPE_NAME");
		stb.append(" FROM (SELECT EM.AGENT_ID, E.MID,e.mer_name, E.COMM_MODE,e.comm_rate,e.comm_amt,E.MCC,");
		stb.append("  (SELECT O.ACCOUNT_NO FROM T_OPERATORS O WHERE O.account_no='"+accountNo+"') ACCOUNT_NO,");
		stb.append(" o.real_name,(SELECT oo.real_name FROM t_operators oo WHERE oo.id = o.supri_id) real_name_up,");
		stb.append(" (SELECT TM.MCC_TYPE FROM T_MCC_CODE TM WHERE TM.MCC_CODE = E.MCC) MCC_TYPE,");
		stb.append(" TT.MERFEE,TT.AMOUNT,tt.termid,tt.etsdate,tt.etstime,tt.account,tt.etstrancode,tt.respcode,tt.reversal");
		stb.append(" FROM T_TRANSJNLS_CALCU TT, ESMIDINFO E,ESMIDINFO_MERINFO EM,t_operators o");
		stb.append(" WHERE TT.MERNO = E.MID AND E.MID = EM.MERCHANT and em.agent_id = o.id AND TT.SETT_FLAG = 'Y'");
		stb.append(" AND TT.ETSDATE >= '"+minDate+"'");
		stb.append(" AND TT.ETSDATE <= '"+maxDate+"'");
		stb.append(" AND em.agent_id IN(");
		stb.append(" SELECT T.ID FROM T_OPERATORS T WHERE T.ROLE_TYPE <> '15' ");
		stb.append(" and  T.ORG_CODE = '"+orgCode+"'");
		stb.append(" START WITH ID = (select id from t_operators where account_no='"+accountNo+"') ");
		stb.append(" CONNECT BY PRIOR T.ID = T.SUPRI_ID )) V,");
		stb.append("(SELECT * FROM t_profit_rule r WHERE r.agent_id=(SELECT s.account_no FROM t_operators s WHERE s.account_no='"+accountNo+"')) TP");
		stb.append("  WHERE V.MCC_TYPE = TP.MCC_TYPE_CODE AND V.COMM_MODE = TP.PARAM_NAME1 and v.account_no = tp.agent_id) X");
		
		return stb.toString();
		
	}

}
